1 Imports System.Data.SqlClient
2 Imports Excel = Microsoft.Office.Interop.Excel
3 Public Class frmAdvanceEntryRecord
4
5 Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
6 Me.Close()
7 End Sub
8
9 Public Sub GetData()
10 Try
11 Total.Visible = True
12 con = New SqlConnection(cs)
13 con.Open()
14 cmd = New SqlCommand("select RTRIM(AdvanceEntry.ID) as [ID], Convert(DateTime,workingdate,131) as [Entry Date],RTRIM(Staff.St_ID) as [SID],RTRIM(Staff.StaffID) as [Staff ID],RTRIM(StaffName) as [Staff Name],RTRIM(Amount) as [Advance] from Advanceentry,Staff where Staff.St_ID=AdvanceEntry.StaffID and Amount > 0 order by workingdate", con)
15 Dim myDA As SqlDataAdapter = New SqlDataAdapter(cmd)
16 Dim myDataSet As DataSet = New DataSet()
17 myDA.Fill(myDataSet, "AdvanceEntry")
18 myDA.Fill(myDataSet, "Staff")
19 dgw.DataSource = myDataSet.Tables("AdvanceEntry").DefaultView
20 dgw.DataSource = myDataSet.Tables("Staff").DefaultView
21 Dim sum As Double = 0
22 For Each r As DataGridViewRow In Me.dgw.Rows
23 sum = sum + r.Cells(5).Value
24 Next
25 sum = Math.Round(sum, 2)
26 TotalAdvance.Text = sum
27 con.Close()
28 Catch ex As Exception
29 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
30 End Try
31 End Sub
32 Private Sub frmLogs_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
33 GetData()
34 End Sub
35 Sub Reset()
36 txtStaffName.Text = ""
37 DateFrom.Text = Today
38 DateTo.Text = Now
39 Total.Visible = False
40 GetData()
41 End Sub
42 Private Sub btnReset_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReset.Click
43 Reset()
44 End Sub
45
46
47 Private Sub btnClose_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
48 Me.Close()
49 End Sub
50
51 Private Sub btnExportExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExportExcel.Click
52 Dim rowsTotal, colsTotal As Short
53 Dim I, j, iC As Short
54 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
55 Dim xlApp As New Excel.Application
56 Try
57 Dim excelBook As Excel.Workbook = xlApp.Workbooks.Add
58 Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
59 xlApp.Visible = True
60
61 rowsTotal = dgw.RowCount
62 colsTotal = dgw.Columns.Count - 1
63 With excelWorksheet
64 .Cells.Select()
65 .Cells.Delete()
66 For iC = 0 To colsTotal
67 .Cells(1, iC + 1).Value = dgw.Columns(iC).HeaderText
68 Next
69 For I = 0 To rowsTotal - 1
70 For j = 0 To colsTotal
71 .Cells(I + 2, j + 1).value = dgw.Rows(I).Cells(j).Value
72 Next j
73 Next I
74 .Rows("1:1").Font.FontStyle = "Bold"
75 .Rows("1:1").Font.Size = 12
76
77 .Cells.Columns.AutoFit()
78 .Cells.Select()
79 .Cells.EntireColumn.AutoFit()
80 .Cells(1, 1).Select()
81 End With
82 Catch ex As Exception
83 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
84 Finally
85 'RELEASE ALLOACTED RESOURCES
86 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
87 xlApp = Nothing
88 End Try
89 End Sub
90
91 Private Sub dgw_MouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles dgw.MouseClick
92 Try
93 Dim dr As DataGridViewRow = dgw.SelectedRows(0)
94 If lblSet.Text = "Advance Entry" Then
95 Me.Hide()
96 frmAdvanceEntry.Show()
97 ' or simply use column name instead of index
98 'dr.Cells["id"].Value.ToString();
99 frmAdvanceEntry.txtID.Text = dr.Cells(0).Value.ToString()
100 frmAdvanceEntry.dtpEntryDate.Text = dr.Cells(1).Value.ToString()
101 frmAdvanceEntry.txtStID.Text = dr.Cells(2).Value.ToString()
102 frmAdvanceEntry.txtStaffID.Text = dr.Cells(3).Value.ToString()
103 frmAdvanceEntry.txtStaffName.Text = dr.Cells(4).Value.ToString()
104 frmAdvanceEntry.txtAmount.Text = dr.Cells(5).Value.ToString()
105 frmAdvanceEntry.btnSave.Enabled = False
106 frmAdvanceEntry.btnUpdate.Enabled = True
107 frmAdvanceEntry.btnDelete.Enabled = True
108 frmAdvanceEntry.dtpEntryDate.Enabled = False
109 End If
110 Catch ex As Exception
111 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
112 End Try
113
114 End Sub
115
116 Private Sub dgw_RowPostPaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgw.RowPostPaint
117 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
118 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
119 If dgw.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
120 dgw.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
121 End If
122 Dim b As Brush = SystemBrushes.ControlText
123 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
124
125 End Sub
126
127 Private Sub txtStaffName_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtStaffName.TextChanged
128 Try
129 Total.Visible = True
130 con = New SqlConnection(cs)
131 con.Open()
132 cmd = New SqlCommand("select RTRIM(AdvanceEntry.ID) as [ID], Convert(DateTime,workingdate,131) as [Entry Date],RTRIM(Staff.St_ID) as [SID],RTRIM(Staff.StaffID) as [Staff ID],RTRIM(StaffName) as [Staff Name],RTRIM(Amount) as [Advance] from Advanceentry,Staff where Staff.St_ID=AdvanceEntry.StaffID and Amount > 0 and StaffName like '" & txtStaffName.Text & "%' order by workingdate", con)
133 Dim myDA As SqlDataAdapter = New SqlDataAdapter(cmd)
134 Dim myDataSet As DataSet = New DataSet()
135 myDA.Fill(myDataSet, "AdvanceEntry")
136 myDA.Fill(myDataSet, "Staff")
137 dgw.DataSource = myDataSet.Tables("AdvanceEntry").DefaultView
138 dgw.DataSource = myDataSet.Tables("Staff").DefaultView
139 Dim sum As Double = 0
140 For Each r As DataGridViewRow In Me.dgw.Rows
141 sum = sum + r.Cells(5).Value
142 Next
143 sum = Math.Round(sum, 2)
144 TotalAdvance.Text = sum
145 con.Close()
146 Catch ex As Exception
147 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
148 End Try
149 End Sub
150
151 Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
152 Try
153 Total.Visible = True
154 con = New SqlConnection(cs)
155 con.Open()
156 cmd = New SqlCommand("select RTRIM(AdvanceEntry.ID) as [ID], Convert(DateTime,workingdate,131) as [Entry Date],RTRIM(Staff.St_ID) as [SID],RTRIM(Staff.StaffID) as [Staff ID],RTRIM(StaffName) as [Staff Name],RTRIM(Amount) as [Advance] from Advanceentry,Staff where Staff.St_ID=AdvanceEntry.StaffID and Amount > 0 and WorkingDate Between @d1 and @d2 order by workingdate", con)
157 cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "DateIN").Value = DateFrom.Value.Date
158 cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "DateIN").Value = DateTo.Value
159 Dim myDA As SqlDataAdapter = New SqlDataAdapter(cmd)
160 Dim myDataSet As DataSet = New DataSet()
161 myDA.Fill(myDataSet, "AdvanceEntry")
162 myDA.Fill(myDataSet, "Staff")
163 dgw.DataSource = myDataSet.Tables("AdvanceEntry").DefaultView
164 dgw.DataSource = myDataSet.Tables("Staff").DefaultView
165 Dim sum As Double = 0
166 For Each r As DataGridViewRow In Me.dgw.Rows
167 sum = sum + r.Cells(5).Value
168 Next
169 sum = Math.Round(sum, 2)
170 TotalAdvance.Text = sum
171 con.Close()
172 Catch ex As Exception
173 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
174 End Try
175 End Sub
176 End Class
